// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 加载MD5模块
const md5 = require('md5');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: 'qwer153039',        //数据库用户密码
  database: 'bookstore',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();
server.use(bodyParser.urlencoded({
  extended: false
}));
// 加载CORS模块
const cors = require('cors');
const { strictEqual } = require('assert');
// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));

// 获取首页 vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvOVER
server.get('/booktype', (req, res) => {
  // SQL语句以获取小说类型表的数据
  // console.log(req.query)
    let booktype=req.query;
    console.log(booktype)
    let sql= 'SELECT * FROM book_type'
    pool.query(sql,[booktype],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});


server.get('/bookname', (req, res) => {
  // SQL语句以获取小说类型小说表的数据
    let bookname=req.query.family_id;
    let sql= `SELECT * FROM book_laptop where family_id=${bookname}`
    pool.query(sql,(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});

server.get('/booknav', (req, res) => {
  // SQL语句以获取主页轮播图的数据
  console.log(req.query)
    let booknav=req.query;
    console.log(booknav)
    let sql= 'SELECT * FROM book_nav'
    pool.query(sql,[booknav],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});
// roll
server.get('/bookroll', (req, res) => {
  // SQL语句以获取主页轮播图的数据
  console.log(req.query)
    let bookroll=req.query;
    let sql= 'SELECT * FROM book_roll'
    pool.query(sql,[bookroll],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});

// 获取详情页vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvOVER
server.get('/bookdetail', (req, res) => {
  // SQL语句以获取主页轮播图的数据
  console.log(req.query)
    let bookdetail=req.query.id;
    let sql= 'SELECT * FROM book_laptop where id=?'
    pool.query(sql,[bookdetail],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});

// 获取最新上架页vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvOVER
server.get('/booknew', (req, res) => {
  // SQL语句以获取主页轮播图的数据
    let bookpage=(req.query.page-1)*8;
    console.log(bookpage)
    let sql= 'select * from book_new limit ?,8;'
    pool.query(sql,[bookpage],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});


// 获取排行榜页vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvOVER
server.get('/bookbang', (req, res) => {
  // SQL语句以获取主页轮播图的数据
  console.log(req.query)
    let bookbang=req.query.bang_id;
    let sql= 'SELECT * FROM book_bang where bang_id=?'
    pool.query(sql,[bookbang],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});

// 获取专题页vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvOVER
server.get('/booksplone', (req, res) => {
  console.log(req.query)
    let booksplone=req.query;
    let sql= 'SELECT * FROM book_specialone'
    pool.query(sql,[booksplone],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});
server.get('/bookspltwo', (req, res) => {
  console.log(req.query)
    let bookspltwo=req.query;
    let sql= 'SELECT * FROM book_specialtwo'
    pool.query(sql,[bookspltwo],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});
server.get('/booksplthree', (req, res) => {
  // SQL语句以获取主页轮播图的数据
  console.log(req.query)
    let booksplthree=req.query;
    let sql= 'SELECT * FROM book_specialthree'
    pool.query(sql,[booksplthree],(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});

//模糊查询
server.get('/booksearch', (req, res) => {
  // 
  console.log(req.query.title)
    let booksearch=req.query.title;
    let sql= `SELECT * FROM book_laptop where title like "%${booksearch}%"`
    pool.query(sql,(error, results) => {
      console.log(results)
      if (error) throw error;
      res.send(results);
    });  
});

// 注册vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvOVER
server.post('/register', (req, res) => {
  //console.log(md5('12345678'));
  // 获取用户名和密码信息
  let name = req.body.name;
  let upwd = req.body.upwd;
  let phone= req.body.phone;
  //以uname为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(id) AS count FROM book_user WHERE name=?';
  pool.query(sql, [name], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT book_user(name,upwd,phone) VALUES(?,MD5(?),?)';
      pool.query(sql, [name, upwd,phone], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
});
// 用户登录接口
server.post('/login', (req, res) => {
  //获取用户名和密码信息
  let name = req.body.name;
  let upwd = req.body.upwd;
  // SQL语句
  let sql = 'SELECT id,name FROM book_user WHERE name=? AND upwd=MD5(?)';
  pool.query(sql, [name, upwd], (error, results) => {
    if (error) throw error;
    if(results.length == 0){ //登录失败
      res.send({message:'login failed',code:201});
    } else {                 //登录成功
      res.send({message:'ok',code:200,result:results[0]});
    }
  });
});
// 添加商品到我的购物车 vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv成功 
server.post('/shopping',(req,res)=>{
  // console.log(req.body)
  let {title,uname,numbers,img_small,price,ischeck,fid} =req.body;
  let sqlsearch = 'select * from book_shopping where uname=? and fid=?';
  pool.query(sqlsearch,[uname,fid],(error,result)=>{
    // console.log(result)
    if (error) throw error;
    else if(result.length==0){
      // console.log('执行添加数据')
      let sql = 'insert book_shopping(fid,title,uname,numbers,img_small,price,ischeck) value(?,?,?,?,?,?,?);'
      pool.query(sql,[fid,title,uname,numbers,img_small,price,ischeck],(error,results)=>{
        if (error) throw error;
        res.send({message:'恭喜您,添加成功',add:true,results});
        console.log(results)
      })
    }else{
      let numold=result[0].numbers
      // console.log('执行增加')
      pool.query('update book_shopping set  numbers=? where uname=? and fid=?',[Number(numbers)+Number(numold),uname,fid],(error,result)=>{
        if (error) throw error;
        res.send({message:'你好~ 购物车已经有该商品 已帮你添加数量',add:false})
      })
    }
  })
})
// 查询购物车中商品  get  vvvvvvvvvvvvvvvv
server.get('/shopcar',(req,res)=>{
  let {uname} = req.query 
         //购物车页面查询
    let sql = 'select * from book_shopping where uname=?'
    pool.query(sql,[uname],(error,result)=>{
      if (error) throw error;
      res.send(result);
    })    
});
// 删除单个商品    post   vvvvvvvvvvvvvvvvvvvvvvvvvvv
server.post('/remove',(req,res)=>{
  // console.log(req.query);
  let {fid} = req.body;
  let sql = 'delete from book_shopping where fid=?'
  pool.query(sql,[fid],(error,result)=>{
    if (error) throw error;
    res.send({message:'删除成功'});  
  });
})     
// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('server is running...');
});